
package DBLag;

import ModelLag.Employee;
import ModelLag.Lane;
import ModelLag.LaneError;
import ModelLag.LaneErrorDescription;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
// implementeret abstract - kan ses i bunden ellers melder den fejl.
/**
 * Databaseklasse for LaneError som står for kommunikationen til databasen og
 * for opbygningen af LaneError objekter som skal bruges i de højere lag.
 * @author DM75 Gruppe 3
 * @version 2011-12-22
 */
public class DbLaneError implements IFDbLaneError 
{
    // Forbindelse til databasen.
    private Connection con;
    /** 
     * En instans variabel af typen PreparedStatement som indeholder et PreparedStatement
     * til brug ved findmetode.
     */
    PreparedStatement pStmtSelectWithDate;
    PreparedStatement pStmtERRsThisMonth;
    private DbEmployee employees;
    private DbLane lanes;
    private boolean debug = false; //hvis den er true tager den alle System.out.println med hvis false printer den ikke ud. 
    
    /**
     * 
     */
    public DbLaneError()
    {
        con = DbConnection1.getInstance().getDBcon();
        pStmtSelectWithDate = null;
        pStmtERRsThisMonth = null;
        employees = new DbEmployee();
        lanes = new DbLane();
    }
    
   
    /**
     * her finder man laneErrors ved hjælp af en date som bliver brugt i et højere lag. 
     * @param date
     * @param retriveAssociation
     * @return en ArrayListe med 
     */
    public ArrayList<LaneError> findLaneErrorWithDate(GregorianCalendar date, boolean retriveAssociation)
    {
        if(pStmtSelectWithDate == null)
        {
            pStmtSelectWithDate = getPreparedStmtWithDate();
        }
        LaneError laneErrObj = null;
        ResultSet results = null;
        ArrayList<LaneError> list = new ArrayList<>();
        try
        {
            java.sql.Date tempDate = new java.sql.Date(date.getTimeInMillis());
            pStmtSelectWithDate.setDate(1, tempDate);
            
            results = pStmtSelectWithDate.executeQuery();
            pStmtSelectWithDate.setQueryTimeout(5);
            results = pStmtSelectWithDate.executeQuery();
            DbEmployee dbEmp = new DbEmployee();
            while(results.next())
             
            {
                laneErrObj = buildLaneError(results);
                laneErrObj.setEmployee(dbEmp.findEmployee(laneErrObj.getEmployee().getCprNo()));
                list.add(laneErrObj);
            }
            
        }

        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
    return list;
    }
    
    // pStmtSelect må ikke lukkes(.close) i findLaneError(), da man bruger det hver gang man skal finde et LaneError objekt.
    /**
     * Finder en LaneError i databasen og sørger for at det bliver bygget op til et 
     * objekt ved hjælp af buildLaneError() og cprnummer på medarbejder, dato, og lokation.
     * @param searchString, en String hvor man både kan søge på date, cprNo, laneNo og errorCode.
     * CPR-Nummer på medarbejderen. Skal anføres DDMMYY-XXXX.
     * @return Hvis fundet returneres et LaneError objekt ellers null.
     */

    public ArrayList<LaneError> findLaneError(String searchString, boolean retriveAssociation)
    {

        LaneError laneErrorObj = null;
        ArrayList<LaneError> laneErrorList = new ArrayList<>();
        ResultSet results = null;
        PreparedStatement pstmt = null;
        
        try
        {
            String query = "SELECT * FROM LaneError "
                        + "WHERE date LIKE '%" + searchString + "%' OR cprNo LIKE '%" 
                        + searchString + "%' OR laneNo LIKE '%"     
                        + searchString + "%' OR errorCode LIKE '%" + searchString + "%'";
            if (debug)
                {
                    System.out.println(query);
                }
            
            pstmt = con.prepareStatement(query);
            results = pstmt.executeQuery();
            
            
            while(results.next())
            {
                laneErrorObj = buildLaneError(results);
                if(retriveAssociation)
                    {               
                        IFDbEmployee dbEmp = new DbEmployee();
                        String cprNo1 = laneErrorObj.getEmployee().getCprNo();
                        Employee employeeObj = dbEmp.findEmployee(cprNo1);
                        laneErrorObj.setEmployee(employeeObj);
                        
                        IFDbLane dbLane = new DbLane();
                        int laneNo = laneErrorObj.getLane().getLaneNo();
                        Lane laneObj = dbLane.findLane(laneNo, false);
                        laneErrorObj.setLane(laneObj);
                     }
                laneErrorList.add(laneErrorObj);

                }
            //pstmt.close();   
          }
            
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return laneErrorList;
    }
        
    /**
     * @return en Array liste af alle de fejl koder der er gemt i vores database.
     * henter dem op fra DB og indsætter dem i et Array.
     * til brug når der skal oprettes en fejl, ses i en comboBox i GUILag
     */
    public ArrayList<LaneErrorDescription> getAllLaneErrorCodes()
    {
        ArrayList<LaneErrorDescription> errorList = new ArrayList<>();

        PreparedStatement pstmt = null;
        
        String allLaneErrorDescriptions = "SELECT errorCode FROM LaneErrorDescription";
        if(debug)
        {
           System.out.println(allLaneErrorDescriptions);  
        }

        LaneErrorDescription laneErrorDescripObj = null;
        ResultSet results;
        try
        {
            pstmt = con.prepareStatement(allLaneErrorDescriptions);
            pstmt.setQueryTimeout(5);
            results = pstmt.executeQuery();
            DbEmployee dbEmp = new DbEmployee();
            while(results.next())
             
            {
                laneErrorDescripObj = buildLaneErrorCodes(results);
                
                errorList.add(laneErrorDescripObj);
            }
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return errorList;
    }
   
    /**
     * Denne metode henter alle de fejl der allerede er gemt i DB(databasen).
     * 
     * @return ArrayList
     */
    public ArrayList<LaneError> getAllLaneErrors()
    {
        ArrayList<LaneError> list = new ArrayList<>();

        PreparedStatement pstmt = null;
        
        String allLaneError = "SELECT * FROM LaneError";
        if(debug)
        {
        System.out.println(allLaneError);
        }
        LaneError laneErrorObj = null;
        ResultSet results;
        try
        {
            pstmt = con.prepareStatement(allLaneError);
            pstmt.setQueryTimeout(5);
            results = pstmt.executeQuery();
            DbEmployee dbEmp = new DbEmployee();
            while(results.next())
             
            {
                laneErrorObj = buildLaneError(results);
                laneErrorObj.setEmployee(dbEmp.findEmployee(laneErrorObj.getEmployee().getCprNo()));
                list.add(laneErrorObj);
            }
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return list;
    }
    
    /**
     * Metoden der bruges til at få de nye fejl indsat i Databasen.
     * bruger laneErrorObj til at oprette selvefejlen og date for at kunne indsætte den på en given dato.
     * @param laneErrorObj
     * @param date
     * @return int
     */
    public int insertLaneError (LaneError laneErrorObj, GregorianCalendar date)
            
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String insert = "INSERT INTO laneError (errorID, description, date, time, cprNo, laneNo, errorCode)" 
                      + " VALUES (?,?,?,?,?,?,?)";
        if(debug)
        {
        System.out.println(insert);
        }
        if(getNextErrorID() != -1)
        {
            
            try
            {
                pstmt = con.prepareStatement(insert);
                pstmt.setQueryTimeout(5);
                pstmt.setInt(1, getNextErrorID());
                
                pstmt.setString(2, laneErrorObj.getDescription());  
                
                long dateString = laneErrorObj.getDate().getTimeInMillis();
                System.out.println(laneErrorObj.getTime());
                if(debug)
                {
                    System.out.println("print 1, i insertLaneError"); 
                }
                
                //Date newDate = new Date();
                java.sql.Date tempDate = new java.sql.Date(dateString);
                
                
                pstmt.setDate(3, tempDate);
                java.util.Date newTime = new java.util.Date();
                java.sql.Time newtime2 = new java.sql.Time(newTime.getTime());
                pstmt.setTime(4, newtime2);
                pstmt.setString(5, laneErrorObj.getEmployee().getCprNo());
                pstmt.setInt(6, laneErrorObj.getLane().getLaneNo());               
                pstmt.setString(7, laneErrorObj.getErrorCode());
                
                rc = pstmt.executeUpdate();
                pstmt.close();
            }
            catch(SQLException sqlE)
            {
                //returnerer SQL Exception
                System.out.println("SQLException in InsertLaneError " + sqlE.getMessage());
            }
            catch(Exception e)
            {
                System.out.println("Exception in InsertLaneError " + e.getMessage());
            }
            
        }
        return rc;
    }
    
    /**
     * Sletter rækken i Shift tabellen i databasen ved hjælp af parameterne.
     * @param laneNo
     * @param errorCode
     * @param date
     * @param time
     * @return -1 = mislykkedes. 1 = lykkedes
     */
    public int deleteLaneError(int laneNo, String errorCode, GregorianCalendar date, String time)
    {
        int rc = -1;
        PreparedStatement pStmt = null;
        String query = null;
        try
        {
            query = "DELETE FROM LaneError WHERE laneNo = ? AND errorCode = ? AND date = ? AND time = ?";
            pStmt = con.prepareStatement(query);
            pStmt.setInt(1, laneNo);
            pStmt.setString(2, errorCode);
            java.sql.Date tempDate = new java.sql.Date(date.getTimeInMillis());
            pStmt.setDate(3, tempDate);
            pStmt.setString(4, time);
            rc = pStmt.executeUpdate();
            pStmt.close();
            
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQLException in deleteLaneError " +sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println("Exception in deleteLaneError " + e.getMessage());
        }
        return rc;
    }
    
    /**
     * Opdaterer rækken i Shift tabellen i databasen i forhold til shift objektet.
     * @param newDescription
     * @param date
     * @param newTime
     * @param newCprNo
     * @param newLaneNo
     * @param newErrorCode
     * @return -1 = mislykkedes. 1 = lykkedes.
     */
    public int updateLaneError(String newDescription, GregorianCalendar date, String newTime, String newCprNo, int newLaneNo, String newErrorCode)
    {
        int rc = -1;
        try{
            String query = "UPDATE LaneError "
                         + "SET description = ?, date = ?, time = ?, cprNo = ?, laneNo = ?, errorCode = ?"
                         + "WHERE laneNo = ? AND errorCode = ? AND date = ? AND time = ? ";
            try (PreparedStatement pstmt = con.prepareStatement(query)) {
                pstmt.setQueryTimeout(5);
                if(debug)
                {
                System.out.println(query);
                }
                
                java.sql.Date tempDate = new java.sql.Date(date.getTimeInMillis());
                pstmt.setString(1, newDescription);
                pstmt.setDate(2, tempDate);
                pstmt.setString(3, newTime);
                pstmt.setString(4, newCprNo);
                pstmt.setInt(5, newLaneNo);
                pstmt.setString(6, newErrorCode);
                
                rc = pstmt.executeUpdate();
            }
            }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return rc;
    }
       
    /**
     * henter alle de fejl der måtte være i den givne måned.
     * @param month
     * @param year
     * @param retriveAssociation
     * @return Arraylist
     */
    public ArrayList<LaneError> getLaneErrorsMonth(int month, int year, boolean retriveAssociation)
    {       
        if(pStmtERRsThisMonth == null)
        {
            pStmtERRsThisMonth = getPreparedStatementERRWPsMonth();
        }
        LaneError LERRObj = null;
        
        ArrayList<LaneError> listErrors = new ArrayList<>();
        ResultSet results = null;
        
        try
        {
            pStmtERRsThisMonth.setInt(1, month);
            pStmtERRsThisMonth.setInt(2, year);
            results = pStmtERRsThisMonth.executeQuery();
            
            
            while(results.next())
                    {

                        LERRObj = buildLaneError(results);

                        if(retriveAssociation)
                        {
                            IFDbEmployee dbEmp = new DbEmployee();
                            String cprNo1 = LERRObj.getEmployee().getCprNo();

                            Employee employeeObj = dbEmp.findEmployee(cprNo1);
                            LERRObj.setEmployee(employeeObj);

                            IFDbLane dbLane = new DbLane();
                            int laneNo = LERRObj.getLane().getLaneNo();
                            Lane laneObj = dbLane.findLane(laneNo, false);
                            LERRObj.setLane(laneObj);
                            
                        }
            listErrors.add(LERRObj); 
                    }
        }
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return listErrors;
    }
    
    /**
     * 
     * @param results
     * @return 
     */
    private LaneErrorDescription buildLaneErrorCodes(ResultSet results)
    {
        LaneErrorDescription laneErrorDescripObj = new LaneErrorDescription();
        GregorianCalendar calendar = new GregorianCalendar();
        
        try
        {
            laneErrorDescripObj.setErrorCode(results.getString("errorCode"));
               
        }
        catch(Exception e)
        {
            System.out.println("Error building LaneError Object " + e.getMessage());
        }
        if(debug)
        {
            System.out.println("lige inden return i buildLaneErrorCodes");
        }
        
        return laneErrorDescripObj;
    }

    
    private LaneError buildLaneError(ResultSet results)
    {
        LaneError laneErrorObj = new LaneError();
        GregorianCalendar calendar = new GregorianCalendar();
        
        try
        {

            laneErrorObj.setErrorCode(results.getString("errorCode"));
            laneErrorObj.setDescription(results.getString("description"));

            calendar.setTimeInMillis(results.getDate("date").getTime());
            laneErrorObj.setDate(calendar);
            laneErrorObj.setTime(results.getString("time"));

            laneErrorObj.setLane(lanes.findLane(results.getInt("laneNo"), false));
            laneErrorObj.setEmployee(employees.findEmployee(results.getString("cprNo")));
        }
        catch(Exception e)
        {
            System.out.println("Error building LaneError Object " + e.getMessage());
        }  
        if(debug)
        {
            System.out.println("lige inden return i buildLaneError");
        }
        return laneErrorObj;
        
    }
    
    private PreparedStatement getPreparedStmtWithDate()
    {
        try
        {
            String query = "SELECT errorID, description, date, time, cprNo, laneNo, errorCode "
                         + "FROM LaneError " 
                         + "WHERE date = ? ";
            if(debug)
            {
            System.out.println("PreparedStatement " + query);
            System.out.println("PreparedStatementWithDate");
            }
            pStmtSelectWithDate = con.prepareStatement(query);
            pStmtSelectWithDate.setQueryTimeout(5);
        }
        catch(SQLException sqlE)
        {
             System.out.println(sqlE.getMessage());
             System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return pStmtSelectWithDate;
    }
    
    private PreparedStatement getPreparedStatementERRWPsMonth()
    {
        try
        {
            String query = "SELECT errorID, description, date, time, cprNo, laneNo, errorCode "
                         + "FROM LaneError " 
                         + "WHERE MONTH(date) = ? AND YEAR(date) = ?";
            if(debug)
            {
            System.out.println(query);
            }
            pStmtERRsThisMonth = con.prepareStatement(query);
            if(debug)
            {
            System.out.println(query);
            }
            pStmtERRsThisMonth.setQueryTimeout(5);
            if(debug)
            {
            System.out.println(pStmtERRsThisMonth);
            }
        }
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return pStmtERRsThisMonth;
    }
    
    /**
     * Henter det højeste ErrorID i LaneError tabellen i databasen og ligger 1 til for
     * at lave et nyt ErrorID til en ny række.
     * @return ErrorID til ny række.
     */
    private int getNextErrorID()
    {
        String query = null;
        ResultSet results = null;
        int nextInt = 0;
        try
        {
            query = "SELECT MAX(errorID) FROM LaneError";
            try (PreparedStatement pStmt = con.prepareStatement(query)) {
                results = pStmt.executeQuery();
                
                if(results.next())
                {
                    nextInt = results.getInt(1);
                    nextInt++;
                }
            }
            
            return nextInt;
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
            return -1;
        }
    }

}
